package manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * A manager class to manage database connection and execute queries.
 * 
 * @author mohameds.2012, sheryl.tay.2012
 * 
 */

class DatabaseMgr {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	/**
	 * Open database connection and execute a read query. Returns result set for
	 * query.
	 * 
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            sequence of parameters to be used in the SQL statement
	 * @return resultSet a result set object containing results for the executed
	 *         query
	 * @throws SQLException
	 *             if a database connection cannot be opened or an SQL error
	 *             occurs while executing the query
	 */
	ResultSet openAndRead(String sql, Object... param) throws SQLException {
		open();
		setStatement(sql, param);
		rs = stmt.executeQuery();
		return rs;
	}

	/**
	 * Open database connection and execute an update, delete or insert query.
	 * Returns number of rows affected.
	 * 
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            sequence of parameters to be used in the SQL statement
	 * @return numRowsAffected number of rows affected by the executed query
	 */
	int openAndUpdateAndClose(String sql, Object... param) {
		try {
			open();
			setStatement(sql, param);
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return 0;
	}

	/**
	 * Open connection to database. Remember to close connection after use.
	 * 
	 * @throws SQLException
	 *             if a database connection cannot be opened
	 */
	private void open() throws SQLException {
		conn = DatabaseConnectionMgr.open();
	}

	/**
	 * Prepare statement for the opened connection.
	 * 
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            a sequence of parameters (optional) to be used in the SQL
	 *            statement
	 * @throws SQLException
	 *             if the database connection is not open
	 */
	private void setStatement(String sql, Object... param) throws SQLException {
		if (conn == null) {
			throw new SQLException("Connection is not open.");
		}
		stmt = conn.prepareStatement(sql);
		for (int i = 0; i < param.length; i++) {
			stmt.setObject(i + 1, param[i]);
		}
	}

	/**
	 * Close the database connection. Should always be called after opening a
	 * database connection.
	 */
	void close() {
		DatabaseConnectionMgr.close(conn, stmt, rs);
	}
}
